package com.statistics.yfdata.dao;

import com.statistics.yfdata.entity.DataElectricityEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by studio on 2017/4/6.
 */

@Repository
public interface DataElectricityDAO extends JpaRepository<DataElectricityEntity, Integer> {

    @Query(value = "select max.room1_electricity-min.room1_electricity," +
            "max.room2_electricity-min.room2_electricity," +
            "max.room3_electricity-min.room3_electricity," +
            "max.room4_electricity-min.room4_electricity," +
            "max.room5_electricity-min.room5_electricity," +
            "max.room7_electricity-min.room7_electricity," +
            "max.room6_electricity-min.room6_electricity," +
            "max.room8_electricity-min.room8_electricity " +
            "from (select * from data_electricity where mac = :mac and created_at > :start and created_at < :end ORDER BY id DESC limit 1) as max," +
            "(select * from data_electricity where mac = :mac and created_at > :start and created_at < :end ORDER BY id limit 1) as min", nativeQuery = true)
    public String getSubtraction(@Param("mac") String mac, @Param("start") long start, @Param("end") long end);

    @Query(value = "select * from data_electricity where mac = ? and created_at > ? and created_at < ?", nativeQuery = true)
    public List<DataElectricityEntity> findByMac(String mac, long start, long end);

    @Query(value = "SELECT max(room1_current), max(room2_current), " +
            "max(room3_current), max(room4_current), max(room5_current), " +
            " max(room6_current), max(room7_current), " +
            "max(room8_current) FROM data_electricity WHERE " +
            "mac = :mac " +
            "AND created_at > :start " +
            "AND created_at < :end", nativeQuery = true)
    public String getMaxCurrent(@Param("mac") String mac, @Param("start") long start, @Param("end") long end);

    @Query(value = "SELECT max(room1_power), max(room2_power), " +
            " max(room3_power), max(room4_power), " +
            "max(room5_power), max(room6_power), max(room7_power), " +
            "max(room8_power) FROM data_electricity WHERE " +
            "mac = :mac " +
            "AND created_at > :start " +
            "AND created_at < :end", nativeQuery = true)
    public String getMaxPower(@Param("mac") String mac, @Param("start") long start, @Param("end") long end);

}
